package com.ysstech.system.service.impl;

import com.ysstech.common.entity.PageBean;
import com.ysstech.common.exception.BusinessException;
import com.ysstech.common.util.resultutil.RestResult;
import com.ysstech.common.util.resultutil.ResultGenerator;
import com.ysstech.system.service.DataQLService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

@Slf4j
@Service
public class DataQLServiceImpl implements DataQLService {

    @Autowired
    private DataSource dataSource;

    /**
     * 获取数据库表的 字段和对应的类型
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public Map<String, String> getTableColumnNames(String sql) throws Exception {
        PreparedStatement pStemt = null;
        Connection connection = null;
        Map<String, String> columnNames = new LinkedHashMap<>();
        Map<String, Integer> columnCount = new HashMap<>();
        try {
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            connection = this.getConnection();
            pStemt = connection.prepareStatement(sql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                if (columnNames.containsKey(rsmd.getColumnName(i + 1))) {
                    int count = columnCount.get(rsmd.getColumnName(i + 1)) + 1;
                    columnNames.put(rsmd.getColumnName(i + 1) + count, rsmd.getColumnTypeName(i + 1));
                    columnCount.put(rsmd.getColumnName(i + 1), count);
                } else {
                    columnNames.put(rsmd.getColumnName(i + 1), rsmd.getColumnTypeName(i + 1));
                    columnCount.put(rsmd.getColumnName(i + 1), 0);
                }
            }
            if (null == columnNames || columnNames.size() == 0) {
                throw new BusinessException("没有获取到表" + sql + "字段和字段类型！");
            }
        } catch (Exception ex) {
            log.error("获取表" + sql + "字段和字段类型出错出错： " + ex);
            throw new BusinessException("获取表" + sql + "字段和字段类型出错出错： " + ex);
        } finally {
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
        return columnNames;
    }

    /**
     * 通过sql查询数据
     *
     * @param sql
     * @param pageBean
     * @return
     * @throws Exception
     */
    public RestResult queryData(String sql, PageBean pageBean) throws Exception {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            Map<String, Object> map = new HashMap<>();
            Map<String, String> columnNames = this.getTableColumnNames(sql);
            connection = this.getConnection();
            pStemt = connection.prepareStatement("select t.*,rownum from (" + sql + ")t where  rownum >= " +
                    (pageBean.getPageNum() - 1) * pageBean.getPageSize() + " and  rownum <= " + pageBean.getPageNum() * pageBean.getPageSize());
            rs = pStemt.executeQuery();
            List<Object> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> stringMap = new HashMap<>();
                int a = 1;
                for (Map.Entry<String, String> info : columnNames.entrySet()) {
                    String colum = info.getKey();
                    String columType = info.getValue();
                    stringMap.put(colum.toLowerCase(), rs.getString(a));
                    a++;
                    continue;
                }
                list.add(stringMap);
            }
            map.put("rows", list);
            pStemt = this.getConnection().prepareStatement("select count(1) as count from (" + sql + ")  t");
            ResultSet rs1 = pStemt.executeQuery();
            if (rs1.next()) {
                map.put("total", rs1.getBigDecimal("count"));
            }
            return ResultGenerator.getSuccessResult("查询数据成功", map);
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            if (rs != null) {   // 关闭记录集
                rs.close();
            }
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
    }

    /**
     * 执行修改语句
     *
     * @param sql
     * @return
     * @throws Exception
     */
    @Transactional
    public RestResult updateData(String sql) throws Exception {
        Statement statement = null;
        Connection connection = null;
        int i = 0;
        int size = 0;
        try {
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            connection = this.getConnection();
            statement = connection.createStatement();
            if (sql.trim().startsWith("declare")) {
                statement.addBatch(sql);
                statement.executeBatch();
            } else {
                String[] arr = sql.split(";");
                int batchNumber = 1000;
                if (null == arr || arr.length == 0) {
                    return ResultGenerator.getFailResult("没有获取到修改语句信息，请稍后重试！");
                }
                for (String sqlstr : arr) {
                    i++;
                    statement.addBatch(sqlstr);
                    if (i % batchNumber == 0) {
                        int[] intarr = statement.executeBatch();
                        size += intarr[0];
                        statement.clearBatch();
                    }
                }
                if (i % batchNumber != 0) {
                    int[] intarr = statement.executeBatch();
                    size += intarr[0];
                    statement.clearBatch();
                }
            }
        } catch (Exception ex) {
            throw new BusinessException("执行数据失败！" + ex.getMessage());
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {   // 关闭声明
                connection.close();
            }
        }
        return ResultGenerator.getSuccessResult("执行语句成功，影响条数" + size);
    }


    private Connection getConnection() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        return DriverManager.getConnection("jdbc:oracle:thin:@192.168.32.203:1521:orcl", "indbadmin", "indbadmin");
        // return dataSource.getConnection();
    }
}
